package com._520it.day2._01_tx;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com._520it.util.JdbcUtil;

public class TxTest {
	
	@Test
	public void testTrans() throws Exception {
		Connection conn = JdbcUtil.getConn();
		//查询张无忌账户是否有1000
		String sql="SELECT * FROM account WHERE name= ? AND balance >=?";
		PreparedStatement ps=conn.prepareStatement(sql);
		ps.setString(1, "张无忌");
		ps.setInt(2, 1000);
		ResultSet rs = ps.executeQuery();
		if (!rs.next()) {
			throw new RuntimeException("余额不足！");
		}
		//把张无忌账户减少1000
		sql="UPDATE account SET balance=balance - ? WHERE name=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, 1000);
		ps.setString(2, "张无忌");
		ps.executeUpdate();
		//把赵敏账户减少1000
		sql="UPDATE account SET balance=balance + ? WHERE name=?";
		ps=conn.prepareStatement(sql);
		ps.setInt(1, 1000);
		ps.setString(2, "赵敏");
		ps.executeUpdate();
		
		JdbcUtil.close(conn, ps, rs);
		
	}
	
	@Test
	public void testTx()  {
		Connection conn = JdbcUtil.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			//设置事务为手动提交
			conn.setAutoCommit(false);
			//查询张无忌账户是否有1000
			String sql="SELECT * FROM account WHERE name= ? AND balance >=?";
			ps=conn.prepareStatement(sql);
			ps.setString(1, "张无忌");
			ps.setInt(2, 1000);
			rs = ps.executeQuery();
			if (!rs.next()) {
				throw new RuntimeException("余额不足！");
			}
			//把张无忌账户减少1000
			sql="UPDATE account SET balance=balance - ? WHERE name=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, 1000);
			ps.setString(2, "张无忌");
			ps.executeUpdate();
			//把赵敏账户减少1000
			sql="UPDATE account SET balance=balance + ? WHERE name=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, 1000);
			ps.setString(2, "赵敏");
			ps.executeUpdate();
			conn.commit();//提交事务
		} catch (Exception e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally{
			JdbcUtil.close(conn, ps, rs);
		}
	
		
	}
}
